﻿using System;
using System.Collections;
using System.Data.SqlClient;
using System.IO;

/// <summary>
/// Represents a data file containing data percentages or averages
/// </summary>
public class DataFileDefinition : FileDefinition
{
    /// <summary>
    /// Creates a DataFileDefinition object describing a certain data set
    /// </summary>
    /// <param name="eth">The ethnicity in common with all this data</param>
    /// <param name="field">The field in common with this data</param>
    /// <param name="desc">A user-visible description of this file</param>
    public DataFileDefinition(Ethnicity eth, Field field, string desc)
        : base(eth, field, desc)
    {
    }

    /// <summary>
    /// Converts the CoupleType value into the database key for couple type
    /// </summary>
    /// <param name="c">The input CoupleType</param>
    /// <returns>The database key</returns>
    private string DbCouple(CoupleType c)
    {
        switch (c)
        {
            case CoupleType.DifferentSexMarried:
                return "DSM";
            case CoupleType.DifferentSexUnmarried:
                return "DSU";
            case CoupleType.SameSexAll:
                return "SSA";
            case CoupleType.SameSexFemale:
                return "SSF";
            case CoupleType.SameSexMale:
                return "SSM";
            default:
                throw new InvalidDataException("Invalid Couple Type");
        }
    }

    /// <summary>
    /// Converts the input Ethnicity into the database key for that ethnicity
    /// </summary>
    /// <param name="e">The input Ethnicity</param>
    /// <returns>The database key</returns>
    private string DbEthnicity(Ethnicity e)
    {
        switch (e)
        {
            case Ethnicity.AfricanAmerican:
                return "AA";
            case Ethnicity.AlaskanNativeAmericanIndian:
                return "ANAI";
            case Ethnicity.All:
                return "ALL";
            case Ethnicity.AsianPacificIslander:
                return "API";
            case Ethnicity.Latino:
                return "L";
            case Ethnicity.White:
                return "W";
            default:
                throw new InvalidDataException("Invalid Ethnicity");
        }
    }

    /// <summary>
    /// Converts the input Field into the database column name to retrieve
    /// </summary>
    /// <param name="f">The input Field</param>
    /// <returns>The name of the column in the database for that field</returns>
    private string DbField(Field f)
    {
        switch (f)
        {
            case Field.Age:
                return "AvgAge";
            case Field.Children:
                return "Children";
            case Field.Education:
                return "Education";
            case Field.Income:
                return "AvgIncome";
            case Field.Population:
                return "PopPct";
            case Field.Veteran:
                return "Veteran";
            default:
                throw new InvalidDataException("Invalid Field");
        }
    }

    /// <summary>
    /// Called once for each tuple loaded from the file
    /// </summary>
    /// <param name="state">The state field of the tuple</param>
    /// <param name="coupleType">The coupleType field of the tuple</param>
    /// <param name="value">The value of the (state, coupletype, ethnicity, field) -> value tuple</param>
    private void AddTuple(string state, CoupleType coupleType, double? value)
    {
        // tuple: (State, CoupleType, Ethnicity, Field) -> value
        // database is (State, CoupleType, Ethnicity, [field1], [field2], [field3], ...)
        // so...
        string table = "acs2007";
        //string field = DbField(Field);

        /*string sql = "IF NOT EXISTS (SELECT State FROM " + table + " WHERE State=@State AND Race=@Race AND Couple=@Couple) " +
            "INSERT INTO " + table + " (State, Race, Couple, " + field + ") VALUES (@State, @Race, @Couple, @Value) " +
            "ELSE " +
            "UPDATE " + table + " SET " + field + " = @Value WHERE State = @State AND Race = @Race AND Couple = @Couple";
        */
        SqlConnection conn = Database.Connection;
        /*SqlCommand cmd = new SqlCommand(sql, conn);
        cmd.Parameters.Add("State", System.Data.SqlDbType.Char, 2).Value = state;
        cmd.Parameters.Add("Race", System.Data.SqlDbType.Char, 4).Value = DbEthnicity(Ethnicity);
        cmd.Parameters.Add("Couple", System.Data.SqlDbType.Char, 3).Value = DbCouple(coupleType);
        cmd.Parameters.Add("Value", System.Data.SqlDbType.Float).Value = value.HasValue ? (object)value.Value : (object)DBNull.Value;
        cmd.ExecuteNonQuery();*/
        SqlCommand cmd = new SqlCommand("InsertRecord", conn);
        cmd.CommandType = System.Data.CommandType.StoredProcedure;
        cmd.Parameters.AddWithValue("@table_name", table);
        cmd.Parameters.AddWithValue("@state", state);
        cmd.Parameters.AddWithValue("@race", DbEthnicity(Ethnicity));
        cmd.Parameters.AddWithValue("@couple", DbCouple(coupleType));
        cmd.Parameters.AddWithValue("@field", DbField(Field));
        cmd.Parameters.AddWithValue("@value", value.HasValue ? (object)value.Value : (object)DBNull.Value);
        cmd.ExecuteNonQuery();
    }

    /// <summary>
    /// Parses the state field in the CSV file to obtain just the two-letter postal
    /// abbreviation of the state
    /// </summary>
    /// <param name="state">The CSV represntation of the state</param>
    /// <returns>The two-letter postal abbreviation of the state, or throws InvalidDataException
    /// in the event of an error.</returns>
    private string ParseState(string state)
    {
        string[] parts = state.Split('/');
        if (parts.Length == 2 && parts[1].Length == 2)      // 01 .Alabama/AL
            return parts[1].ToUpperInvariant();
        else if (state.Length == 2)                         // CA
            return state.ToUpperInvariant();
        else if (state.StartsWith("st="))                  // st=1
        {
            int stateno;
            if (state[3] == '=')    // most have double-equals
                stateno = Int32.Parse(state.Substring(4));
            else
                stateno = Int32.Parse(state.Substring(3));

            // This is the ugly: some of our files have state information in this format.
            // Deal with it for now, but try and phase it out.
            string[] statedefs = new string[] {
                    "AL", "AK",
                    "",     // 03 (unknown)
                    "AZ", "AR", "CA",
                    "",     // 07 (unknown)
                    "CO", "CT", "DE", "DC", "FL", "GA",
                    "",     // 14 (unknown)
                    "HI", "ID", "IL", "IN", "IA", "KS", "KY", "LA", "ME", "MD", "MA", "MI", "MN", "MS", "MO",
                    "MT", "NE", "NV", "NH", "NJ", "NM", "NY", "NC", "ND", "OH", "OK", "OR", "PA",
                    "",     // 43 (unknown)
                    "RI", "SC", "SD", "TN", "TX", "UT", "VT", "VA",
                    "",     // 52 (unknown)
                    "WA", "WV", "WI", "WY"
            };

            state = statedefs[stateno-1];
            if (state.Length != 2)
                throw new InvalidDataException("Undefined state number: " + stateno);
            return state;
        }
        else
            throw new InvalidDataException("Unknown state: " + state);
    }

    /// <summary>
    /// Parses a CSV data file line by line to extract the data
    /// </summary>
    /// <param name="data">The CSV file to extract data from</param>
    /// <returns>True if importing this document was successful</returns>
    public override bool ProcessData(StreamReader data)
    {
        string headerline = data.ReadLine();
        string[] cols = ParseCSV(headerline);

        // only do anything if we have at least 6 cols: state, DSM, DSU, SSA, SSM, SSF
        if (cols == null || cols.Length < 6)
            throw new InvalidDataException("Data does not have at least 6 columns");

        // figure out which column is what.  First is always state, then the rest are
        // "Married DS" "Unmarried DS" "All SS Couples" "Female SS" "Male SS"
        CoupleType[] coldefs = new CoupleType[cols.Length];
        int contains = 0;
        for (int i = 0; i < cols.Length; i++)
        {
            string colname = cols[i].ToLowerInvariant();
            if (colname == "married ds")
            {
                coldefs[i] = CoupleType.DifferentSexMarried;
                contains |= 0x01;
            }
            else if (colname == "unmarried ds")
            {
                coldefs[i] = CoupleType.DifferentSexUnmarried;
                contains |= 0x02;
            }
            else if (colname == "all ss couples")
            {
                coldefs[i] = CoupleType.SameSexAll;
                contains |= 0x04;
            }
            else if (colname == "female ss")
            {
                coldefs[i] = CoupleType.SameSexFemale;
                contains |= 0x08;
            }
            else if (colname == "male ss")
            {
                coldefs[i] = CoupleType.SameSexMale;
                contains |= 0x10;
            }
            else
            {
                // first column is the state
                if (i == 0)
                    contains |= 0x20;
                coldefs[i] = CoupleType.None;
            }
        }

        // check to ensure complete coverage of state, DSM, DSU, SSA, SSF, SSM
        if (contains != 0x3F)       // (bit set for each coupletype + state
            throw new InvalidDataException("Columns do not include full data set: state, then {DSM, DSU, SSA, SSF, SSM}");

        for (; ; )
        {
            string line = data.ReadLine();
            if (line == null)
                break;
            if (line == String.Empty)   // empty line at the end, maybe?
                continue;
            cols = ParseCSV(line);
            if (cols.Length < 6)
                throw new InvalidDataException("File has line with not enough data");

            // state is first column... figure that one out
            // appears to take the form of "01 .Alabama/AL",
            // where the "==1" matches the "01 ." in the long version.
            string state = ParseState(cols[0]);

            // then go through each of the remaining columns to get the data from them
            for (int i = 1; i < cols.Length; i++)
            {
                if (coldefs[i] == CoupleType.None)
                    continue;

                double? value = null;
                if (cols[i].ToLowerInvariant() == "n/a" || cols[i].ToLowerInvariant() == "na")
                    value = null;
                else
                    value = Double.Parse(cols[i]);

                AddTuple(state, coldefs[i], value);
            }
        }

        return true;
    }

    public void Test(TestResultDelegate ReportResult)
    {
        DataFileDefinition dfd = new DataFileDefinition(Ethnicity.White, Field.Age, "Test file");
        string[] results = dfd.ParseCSV("");
        ReportResult("ParseCSV()", results.Length == 1 && results[0] == "", null);

        results = dfd.ParseCSV("field1");
        ReportResult("ParseCSV(field1)", results.Length == 1 && results[0] == "field1", String.Join("|", results));

        results = dfd.ParseCSV("field1,field2,field3");
        ReportResult("ParseCSV(field1,field2,field3)", results.Length == 3 && results[0] == "field1" && results[1] == "field2" && results[2] == "field3", String.Join("|", results));

        results = dfd.ParseCSV("\"field,containing,comma\"");
        ReportResult("ParseCSV(\"field,containing,comma\")", results.Length == 1 && results[0] == "field,containing,comma", String.Join("|", results));

        results = dfd.ParseCSV("normal,\"with,comma\",normal2");
        ReportResult("ParseCSV(normal,\"with,comma\",normal2)", results.Length == 3 && results[0] == "normal" && results[1] == "with,comma" && results[2] == "normal2", String.Join("|", results));

        results = dfd.ParseCSV("field1,\"with\"\"quote\"");
        ReportResult("ParseCSV(field1,\"with\"\"quote\"", results.Length == 2 && results[0] == "field1" && results[1] == "with\"quote", String.Join("|", results));
    }
}